﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Zyg.SSO.Core.Business;
using Zyg.Utility.DBUtility;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class SqlProvider : DALProvider
    {
        #region Account

        public override Account SelectAccount(Guid id)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, id);
            SqlDataReader reader = db.ExecuteSPReader("usp_SelectAccount");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Account account = new Account();
                if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                reader.Close();

                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        /// <summary>
        /// Insert an account into to database
        /// </summary>
        public override void InsertAccount(Account account)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, account.Id);
            db.AddParameter("@RoleID", SqlDbType.UniqueIdentifier, account.RoleID);
            db.AddParameter("@Email", SqlDbType.VarChar, account.Email);
            db.AddParameter("@Name", SqlDbType.VarChar, account.Name);
            db.AddParameter("@Password", SqlDbType.VarChar, account.Password);
            db.AddParameter("@DateCreated", SqlDbType.DateTime, account.DateCreated);
            db.AddParameter("@State", SqlDbType.Int, (int)account.State);
            db.AddParameter("@IsAdmin", SqlDbType.Bit, account.IsAdmin);
            db.AddParameter("@PictureAddress", SqlDbType.VarChar, account.PictureAddress);
            db.AddParameter("@Telephone", SqlDbType.VarChar, account.Telephone);
            db.AddParameter("@PassQuestion", SqlDbType.VarChar, account.PassQuestion);
            db.AddParameter("@PassAnswer", SqlDbType.VarChar, account.PassAnswer);
            db.AddParameter("@Remark", SqlDbType.VarChar, account.Remark);

            db.ExecuteSP("usp_InsertAccount");
        }

        /// <summary>
        /// Delete account
        /// </summary>
        public override void DeleteAccount(Account account)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, account.Id);

            db.ExecuteSql("Delete Account Where [ID] = @ID");
        }

        /// <summary>
        /// Update account
        /// </summary>
        public override void UpdateAccount(Account account)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@ID", SqlDbType.UniqueIdentifier, account.Id);
            db.AddParameter("@RoleID", SqlDbType.UniqueIdentifier, account.RoleID);
            db.AddParameter("@Email", SqlDbType.VarChar, account.Email);
            db.AddParameter("@Name", SqlDbType.VarChar, account.Name);
            db.AddParameter("@Password", SqlDbType.VarChar, account.Password);
            db.AddParameter("@DateCreated", SqlDbType.DateTime, account.DateCreated);
            db.AddParameter("@State", SqlDbType.Int, (int)account.State);
            db.AddParameter("@IsAdmin", SqlDbType.Bit, account.IsAdmin);
            db.AddParameter("@PictureAddress", SqlDbType.VarChar, account.PictureAddress);
            db.AddParameter("@Telephone", SqlDbType.VarChar, account.Telephone);
            db.AddParameter("@PassQuestion", SqlDbType.VarChar, account.PassQuestion);
            db.AddParameter("@PassAnswer", SqlDbType.VarChar, account.PassAnswer);
            db.AddParameter("@Remark", SqlDbType.VarChar, account.Remark);

            db.ExecuteSP("usp_UpdateAccount");
        }

        public override IList<Business.Account> FillAccounts()
        {
            return FillAccounts(null);
        }

        public override IList<Business.Account> FillAccounts(Role role)
        {
            SqlServerUtility db = new SqlServerUtility();

            IList<Business.Account> accounts = new List<Business.Account>();

            SqlDataReader reader;

            if (role != null)
            {
                db.AddParameter("@WhereCondition", SqlDbType.VarChar, "[RoleID] = '" + role.Id.ToString() + "'");
                // db.AddParameter("@OrderByExpression", SqlDbType.VarChar, "[DateCreated] asc");

                reader = db.ExecuteSPReader("usp_SelectAccountsDynamic");
            }
            else
            {
                reader = db.ExecuteSPReader("usp_SelectAccountsAll");
            }

            if (reader != null)
            {
                while (reader.Read())
                {
                    Account account = new Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                    if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                    if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                    if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                    account.MarkOld();

                    accounts.Add(account);
                }

                reader.Close();
            }

            return accounts;
        }

        public override IList<Account> FillAccounts(string email, string name, AccountState state, bool? isAdmin, Guid? roleId, DateTime? startTime, DateTime? endTime, int? startIndex, int? endIndex)
        {
            IList<Account> results = new List<Account>();

            SqlServerUtility db = new SqlServerUtility();

            StringBuilder sql = new StringBuilder();

            #region Build SQL

            bool paged = startIndex.HasValue && endIndex.HasValue;

            if (paged)
            {
                db.AddParameter("@StartIndex", SqlDbType.Int, startIndex.Value);
                db.AddParameter("@EndIndex", SqlDbType.Int, endIndex.Value);

                sql.Append("SELECT [ID],[RoleID],[Email],[Name],[Password],[DateCreated],[State],[IsAdmin],[PictureAddress],[Telephone],[PassQuestion],[PassAnswer],[Remark],[RowNumber] FROM( ");
            }

            sql.Append("SELECT [ID],[RoleID],[Email],[Name],[Password],[DateCreated],[State],[IsAdmin],[PictureAddress],[Telephone],[PassQuestion],[PassAnswer],[Remark] ");

            if (paged)
                sql.Append(",ROW_NUMBER() OVER(ORDER BY DateCreated) as [RowNumber] ");

            sql.Append(" FROM [Account] WHERE 1=1 ");

            if (!string.IsNullOrEmpty(email))
            {
                db.AddParameter("@Email", SqlDbType.VarChar, email);
                sql.Append("AND [Emial] like '%' + @Email + '%' ");
            }

            if (!string.IsNullOrEmpty(name))
            {
                db.AddParameter("@Name", SqlDbType.VarChar, name);
                sql.Append(" AND [Name] like '%' + @Name + '%' ");
            }

            if (state.ID != Business.AccountState.All.ID)
            {
                db.AddParameter("@State", SqlDbType.Int, (int)state);
                sql.Append(" AND [State] = @State ");
            }

            if (isAdmin.HasValue)
            {
                db.AddParameter("@IsAdmin", SqlDbType.Bit, isAdmin.Value);
                sql.Append(" AND [IsAdmin] = @IsAdmin ");
            }

            if (roleId.HasValue)
            {
                db.AddParameter("@RoleID", SqlDbType.UniqueIdentifier, roleId.Value);
                sql.Append(" AND [RoleID] = @RoleID ");
            }

            if (startTime.HasValue && endTime.HasValue)
            {
                db.AddParameter("@StartTime", SqlDbType.DateTime, startTime.Value);
                db.AddParameter("@EndTime", SqlDbType.DateTime, endTime.Value);

                sql.Append(" AND ([DateCreated] BETWEEN @StartTime AND @EndTime) ");
            }

            if (paged)
            {
                sql.Append(" ) AS OrderedAccount WHERE RowNumber between @StartIndex and @EndIndex;");
            }

            #endregion

            SqlDataReader reader = db.ExecuteSqlReader(sql.ToString());

            if (reader != null)
            {
                while (reader.Read())
                {
                    Account account = new Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                    if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                    if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                    if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                    account.MarkOld();

                    results.Add(account);
                }

                reader.Close();
            }

            return results;
        }

        public override bool ExistsAccount(string email)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@Email", SqlDbType.VarChar, email);
            SqlDataReader reader = db.ExecuteSqlReader("Select [ID] From Account Where [Email] = @Email");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                reader.Close();

                return true;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return false;
            }
        }

        public override Account SelectAccount(string email)
        {
            SqlServerUtility db = new SqlServerUtility();

            db.AddParameter("@WhereCondition", SqlDbType.NVarChar, " [Email]='" + email + "' ");
            SqlDataReader reader = db.ExecuteSPReader("usp_SelectAccountsDynamic");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Account account = new Account();
                if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                account.MarkOld();

                reader.Close();

                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        #endregion




    }
}
